package main;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Maintains database connection.
 * 
 * @author Dawid Nejman
 * @version 2.3 21-12-2011
 */
public class DataConnection {
	private static final String database = "idss";
	private static final Logger log = Logger.getLogger(DataConnection.class
			.getName());
	private static String password;
	private static String username;
	
	static {
		Properties applicationProps =
			    new Properties();

			FileInputStream in;
			try {
				in = new FileInputStream("properties");
				applicationProps.load(in);
				in.close();
				System.out.println("DONE LOADING PROPERTIES");
				username = applicationProps.getProperty("username");
				password = applicationProps.getProperty("password");
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	/**
	 * Adds ITS to Database.
	 * 
	 * @param its
	 *            ITS object ot add.
	 */
	public static void addIts(Its its) {
		Connection con = null;
		Statement statement = null;
		String query = "INSERT INTO `idss`.`TBL_ITS` (`NAME`) VALUES ('"
				+ its.getName() + "')";
		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			// Adds new ITS
			statement.executeUpdate(query);
			int iID = getItsID(its.getName());

			for (Its.Problem problem : its.getProblems()) {
				for (Its.Problem.Trait subproblem : problem.getTraits()) {
					int spID = getSubproblemID(problem.getName(),
							subproblem.getInfluence());
					double value = subproblem.getPerformance();
					query = "INSERT INTO `idss`.`TBL_ITS_SUBPROBLEM` (`VALUE`, `FK_ITS`, `FK_SUBPROBLEM`) VALUES ("
							+ value + ", " + iID + ", " + spID + ")";
					statement.executeUpdate(query);
				}
			}

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}
	}

	/**
	 * Adds Problem to the database. Automatically creates Subproblem named
	 * "Total" and assigns values for it in given years. Also relations in
	 * databases are created automatically, based on received ids.
	 * 
	 * @param problem
	 *            problem instance.
	 */
	public static void addProblem(Problem problem) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = getJDBCConnection();

			// Insert Problem
			preparedStatement = connection.prepareStatement(
					"INSERT INTO `idss`.`TBL_PROBLEM` (`NAME`) VALUES (?);",
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, problem.getName());
			int affectedRows = preparedStatement.executeUpdate();

			if (affectedRows == 0)
				throw new SQLException(
						"Creating problem failed, no rows affected.");
			long pID;
			generatedKeys = preparedStatement.getGeneratedKeys();

			if (generatedKeys.next())
				pID = generatedKeys.getLong(1);
			else
				throw new SQLException(
						"Creating problem failed, no generated key obtained.");

			// Insert Subproblem "Total"
			preparedStatement = connection
					.prepareStatement(
							"INSERT INTO `idss`.`TBL_SUBPROBLEM` (`NAME`) VALUES ('Total');",
							Statement.RETURN_GENERATED_KEYS);
			affectedRows = preparedStatement.executeUpdate();

			if (affectedRows == 0)
				throw new SQLException(
						"Creating subproblem failed, no rows affected.");
			long spID;
			generatedKeys = preparedStatement.getGeneratedKeys();

			if (generatedKeys.next())
				spID = generatedKeys.getLong(1);
			else
				throw new SQLException(
						"Creating subproblem failed, no generated key obtained.");

			// Insert relation Problem - subproblem
			preparedStatement = connection
					.prepareStatement(
							"INSERT INTO `idss`.`TBL_PROBLEM_SUBPROBLEM` (`FK_PROBLEM`, `FK_SUBPROBLEM`) VALUES (?, ?);",
							Statement.NO_GENERATED_KEYS);
			preparedStatement.setInt(1, (int) pID);
			preparedStatement.setInt(2, (int) spID);
			affectedRows = preparedStatement.executeUpdate();

			if (affectedRows == 0)
				throw new SQLException(
						"Creating problem - subproblem relation failed, no rows affected.");

			for (Integer year : problem.getData().keySet()) {
				preparedStatement = connection
						.prepareStatement(
								"INSERT INTO `idss`.`TBL_SUBPROBLEM_SIZE` (`YEAR`, `FK_SUBPROBLEM`, `VALUE`) VALUES (?, ?, ?);",
								Statement.NO_GENERATED_KEYS);
				preparedStatement.setInt(1, (int) year.intValue());
				preparedStatement.setInt(2, (int) spID);
				preparedStatement.setDouble(3, problem.getData().get(year));
				affectedRows = preparedStatement.executeUpdate();

				if (affectedRows == 0)
					throw new SQLException(
							"Creating year - subproblem - size relation failed, no rows affected.");
			}
		} catch (SQLException sqle) {
			log.log(Level.SEVERE, null, sqle);
		}
	}

	/**
	 * Returns statistical data about number of accidents in Sweden.
	 * 
	 * @return map containing pair of date and number of accidents.
	 */
	public static Map<Date, Integer> getAccidentData() {
		Connection con = null;
		Statement statement = null;
		Calendar cal = Calendar.getInstance();
		int accidentID = getSubproblemID("Accidents", "Fatal Accidents");
		String query = "SELECT YEAR, VALUE FROM TBL_SUBPROBLEM_SIZE WHERE FK_SUBPROBLEM="
				+ accidentID;
		HashMap<Date, Integer> accidents = new HashMap<Date, Integer>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				int year = rs.getInt("YEAR");
				cal.set(year, 0, 0);
				Date date = cal.getTime();
				Integer accidentsNo = new Integer(rs.getInt("VALUE"));
				accidents.put(date, accidentsNo);
			}

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return accidents;
	}

	/**
	 * Returns statistical data about number of cars.
	 * 
	 * @return map containing pair of date and number of cars.
	 */
	public static Map<Date, Integer> getCarsData() {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT * FROM TBL_CARS_IN_TRAFFIC";
		HashMap<Date, Integer> cars = new HashMap<Date, Integer>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				Date date = rs.getDate("PK_DATE");
				Integer carsNo = new Integer(rs.getInt("CARS"));
				cars.put(date, carsNo);
			}

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return cars;
	}

	/**
	 * Returns statistical data about emission in Sweden.
	 * 
	 * @return map containing pair of date and tons of CO2.
	 */
	public static Map<Date, Double> getEmissionData() {
		Connection con = null;
		Statement statement = null;
		Calendar cal = Calendar.getInstance();
		int emissionID = getSubproblemID("Emission", "Total");
		String query = "SELECT YEAR, VALUE FROM TBL_SUBPROBLEM_SIZE WHERE FK_SUBPROBLEM="
				+ emissionID;
		HashMap<Date, Double> emission = new HashMap<Date, Double>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				int year = rs.getInt("YEAR");
				cal.set(year, 0, 0);
				Date date = cal.getTime();
				Double emissionSize = new Double(rs.getDouble("VALUE"));
				emission.put(date, emissionSize);
			}

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return emission;
	}

	/**
	 * Returns database ITS ID from {@code PK_ID} column.
	 * 
	 * @param itsName
	 *            name of ITS.
	 * @return ITS ID.
	 */
	public static int getItsID(String itsName) {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT PK_ID FROM TBL_ITS WHERE NAME='" + itsName + "'";
		int iID = -1;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				iID = rs.getInt("PK_ID");

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return iID;
	}

	/**
	 * Returns ITS names stored in Data Base.
	 * 
	 * @return array with ITS names.
	 */
	public static String[] getItsNames() {
		Its[] itsArray = readData();
		String[] names = new String[itsArray.length];
		for (int i = 0; i < itsArray.length; i++)
			names[i] = itsArray[i].getName();
		return names;
	}

	/**
	 * Returns percentage of users using given ITS system.
	 * 
	 * @param itsName
	 *            name of the ITS.
	 * @return users percentage.
	 */
	public static double getItsUsers(String itsName) {
		int iID = getItsID(itsName);
		Connection con = null;
		Statement statement = null;
		String query = "SELECT USERS FROM TBL_ITS WHERE PK_ID=" + iID;
		double result = -1.0;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				result = rs.getDouble("USERS");

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return result;
	}

	/**
	 * Creates connection to database.
	 * 
	 * @return database connection.
	 */
	private static Connection getJDBCConnection() {
		try {
			return (Connection) DriverManager.getConnection(
					"jdbc:mysql://localhost/" + database, username, password);
		} catch (SQLException ex) {
			log.log(Level.SEVERE, null, ex);
			return null;
		}
	}

	/**
	 * Retrives percent of problem reduction for choosen ITS.
	 * 
	 * @param its
	 *            ITS used for problem.
	 * @param problem
	 *            problem to reduce.
	 * @return number of percent that the problem is reduced by ITS.
	 */
	public static double getPercentOfReduction(String itsName,
			String problemName, String subproblemName) {
		double value = 0.0;
		Connection con = null;
		Statement statement = null;
		int sID = getSubproblemID(problemName, subproblemName);
		String query = "SELECT TBL_ITS_SUBPROBLEM.VALUE AS VALUE FROM TBL_ITS_SUBPROBLEM, TBL_ITS "
				+ "WHERE TBL_ITS_SUBPROBLEM.FK_ITS = TBL_ITS.PK_ID AND TBL_ITS.NAME='"
				+ itsName + "' AND TBL_ITS_SUBPROBLEM.FK_SUBPROBLEM=" + sID;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				value = rs.getDouble("VALUE");
			}
			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return value;
	}

	/**
	 * Returns characteristic for a {@code problemName}.
	 * 
	 * @param problemName
	 *            valued name of a problem.
	 * @return characteristics of a problem and number of cars.
	 */
	public static double getProblemCarCharacteristic(String problemName) {
		return getProblemCarCharacteristic(problemName, "Total");
	}

	public static double getProblemCarCharacteristic(String problemName,
			String subproblemName) {
		double ret = 0.0;
		Connection con = null;
		Statement statement = null;
		int spID = getSubproblemID(problemName, subproblemName);
		String query = "SELECT get_characteristic('" + spID + "')";

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				ret = rs.getDouble(1);

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return ret;
	}

	private static int getProblemID(String problemName) {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT TBL_PROBLEM.PK_ID FROM TBL_PROBLEM WHERE NAME='"
				+ problemName + "'";
		int pID = -1;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				pID = rs.getInt("PK_ID");

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return pID;
	}

	/**
	 * Returns problem names stored in Data Base.
	 * 
	 * @return array with problem names.
	 */
	public static String[] getProblemNames() {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT Name FROM TBL_PROBLEM";
		String[] names;
		ArrayList<String> problemNames = new ArrayList<String>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next())
				problemNames.add(rs.getString("Name"));

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		names = new String[problemNames.size()];
		return problemNames.toArray(names);
	}

	private static int getSubproblemID(String problemName, String subproblemName) {
		Connection con = null;
		Statement statement = null;
		int pID = getProblemID(problemName);
		String query = "SELECT TBL_SUBPROBLEM.PK_ID FROM TBL_SUBPROBLEM, TBL_PROBLEM_SUBPROBLEM WHERE TBL_SUBPROBLEM.NAME='"
				+ subproblemName
				+ "' AND TBL_PROBLEM_SUBPROBLEM.FK_PROBLEM="
				+ pID
				+ " AND TBL_PROBLEM_SUBPROBLEM.FK_SUBPROBLEM=TBL_SUBPROBLEM.PK_ID";
		int sID = -1;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				sID = rs.getInt("PK_ID");

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return sID;
	}

	/**
	 * Returns subprolems that belongs to a given problem name.
	 * 
	 * @param problemName
	 *            String with the name of the problem.
	 * @return an array of subproblems.
	 */
	public static String[] getSubproblemNamesForProblem(String problemName) {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT TBL_SUBPROBLEM.NAME AS Name FROM TBL_SUBPROBLEM, TBL_PROBLEM_SUBPROBLEM, TBL_PROBLEM "
				+ "WHERE TBL_SUBPROBLEM.PK_ID=TBL_PROBLEM_SUBPROBLEM.FK_SUBPROBLEM AND TBL_PROBLEM_SUBPROBLEM.FK_PROBLEM=TBL_PROBLEM.PK_ID "
				+ "AND TBL_PROBLEM.NAME='" + problemName + "'";
		String[] names;
		ArrayList<String> subproblemNames = new ArrayList<String>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			while (rs.next())
				subproblemNames.add(rs.getString("Name"));

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		names = new String[subproblemNames.size()];
		return subproblemNames.toArray(names);
	}

	/**
	 * Informs whether ITS exists in Database or not.
	 * 
	 * @param itsName
	 *            name of ITS we want to check.
	 * @return true if ITS exists, false otherwise.
	 */
	public static boolean itsExists(String itsName) {
		String[] itsNames = getItsNames();
		for (String its : itsNames) {
			if (its.equalsIgnoreCase(itsName))
				return true;
		}
		return false;
	}

	private static boolean itsHasDefinedReduction(String itsName,
			String problemName, String subproblemName) {
		boolean ret = false;
		Connection con = null;
		Statement statement = null;
		int sID = getSubproblemID(problemName, subproblemName);
		int iID = getItsID(itsName);
		String query = "SELECT TBL_ITS_SUBPROBLEM.VALUE AS VALUE FROM TBL_ITS_SUBPROBLEM "
				+ "WHERE TBL_ITS_SUBPROBLEM.FK_ITS ="
				+ iID
				+ " AND TBL_ITS_SUBPROBLEM.FK_SUBPROBLEM=" + sID;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next())
				ret = true;

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return ret;
	}

	public static boolean problemAndSubproblemExists(String problemName,
			String subproblemName) {
		int sID = getSubproblemID(problemName, subproblemName);
		if (sID == -1)
			return false;
		return true;
	}

	/**
	 * Returns information whether problem exists.
	 * 
	 * @param problemName
	 *            Name of the problem.
	 * @return true if exits, false otherwise.
	 */
	public static boolean problemExists(String problemName) {
		int pID = getProblemID(problemName);
		boolean ret = false;
		if (pID != -1)
			ret = true;
		return ret;
	}

	/**
	 * Reads data about ITS from database.
	 * 
	 * @return array of ITS.
	 */
	public static Its[] readData() {
		Its[] itsArray = null;
		Connection con = null;
		Statement statement = null;
		String query = "SELECT TBL_ITS.NAME AS ITS_NAME,TBL_PROBLEM.NAME AS PROBLEM_NAME, TBL_SUBPROBLEM.NAME AS TRAIT_NAME, TBL_ITS_SUBPROBLEM.VALUE FROM "
				+ "TBL_ITS, TBL_SUBPROBLEM, TBL_ITS_SUBPROBLEM, TBL_PROBLEM, TBL_PROBLEM_SUBPROBLEM WHERE (TBL_ITS.PK_ID=TBL_ITS_SUBPROBLEM.FK_ITS AND "
				+ "TBL_SUBPROBLEM.PK_ID=TBL_ITS_SUBPROBLEM.FK_SUBPROBLEM AND TBL_PROBLEM.PK_ID=TBL_PROBLEM_SUBPROBLEM.FK_PROBLEM AND "
				+ "TBL_SUBPROBLEM.PK_ID=TBL_PROBLEM_SUBPROBLEM.FK_SUBPROBLEM)";
		HashMap<String, Its> itsMap = new HashMap<String, Its>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			Its its = null;

			while (rs.next()) {
				String name = rs.getString("ITS_NAME");
				String problem = rs.getString("PROBLEM_NAME");
				String subproblem = rs.getString("TRAIT_NAME");
				Float value = rs.getFloat("VALUE");

				if (!itsMap.containsKey(name)) {
					its = new Its(name);
					itsMap.put(name, its);
				} else if (!name.equals(its.getName())) {
					its = itsMap.get(name);
				}

				its.addProblem(problem);
				its.addSubProblem(problem, subproblem, value);
			}
			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		itsArray = new Its[itsMap.size()];
		return itsMap.values().toArray(itsArray);
	}

	/**
	 * Removes all data about given ITS in Database.
	 * 
	 * @param itsName
	 *            Name of ITS.
	 */
	public static void removeIts(String itsName) {
		Connection con = null;
		Statement statement = null;
		int iID = getItsID(itsName);
		String query = "DELETE FROM `idss`.`TBL_ITS_SUBPROBLEM` WHERE `FK_ITS`='"
				+ iID + "'";

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			statement.executeUpdate(query);

			query = "DELETE FROM `idss`.`TBL_ITS` WHERE NAME = '" + itsName
					+ "'";
			statement.executeUpdate(query);

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

	}

	/**
	 * Removes a problem, its subproblems and all connections alongside.
	 * 
	 * @param problemName
	 *            valid name of the problem.
	 */
	public static void removeProblem(String problemName) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		if (!problemExists(problemName))
			return;

		String[] subproblemNames = getSubproblemNamesForProblem(problemName);
		int pID = getProblemID(problemName);

		try {
			connection = getJDBCConnection();

			// DELETE from TBL_SUBPROBLEM_SIZE, TBL_ITS_SUBPROBLEM,
			// TBL_PROBLEM_SUBPROBLEM and TBL_SUBPROBLEM
			for (int i = 0; i < subproblemNames.length; i++) {
				int spID = getSubproblemID(problemName, subproblemNames[i]);
				preparedStatement = connection
						.prepareStatement(
								"DELETE FROM `idss`.`TBL_SUBPROBLEM_SIZE` WHERE `FK_SUBPROBLEM`=?",
								Statement.NO_GENERATED_KEYS);
				preparedStatement.setInt(1, spID);
				preparedStatement.executeUpdate();

				preparedStatement = connection
						.prepareStatement(
								"DELETE FROM `idss`.`TBL_ITS_SUBPROBLEM` WHERE `FK_SUBPROBLEM`=?",
								Statement.NO_GENERATED_KEYS);
				preparedStatement.setInt(1, spID);
				preparedStatement.executeUpdate();

				preparedStatement = connection
						.prepareStatement(
								"DELETE FROM `idss`.`TBL_PROBLEM_SUBPROBLEM` WHERE `FK_SUBPROBLEM`=? AND `FK_PROBLEM`=?",
								Statement.NO_GENERATED_KEYS);
				preparedStatement.setInt(1, spID);
				preparedStatement.setInt(2, pID);
				preparedStatement.executeUpdate();

				preparedStatement = connection.prepareStatement(
						"DELETE FROM `idss`.`TBL_SUBPROBLEM` WHERE `PK_ID`=?",
						Statement.NO_GENERATED_KEYS);
				preparedStatement.setInt(1, spID);
				preparedStatement.executeUpdate();
			}

			// DELETE from TBL_PROBLEM
			preparedStatement = connection.prepareStatement(
					"DELETE FROM `idss`.`TBL_PROBLEM` WHERE `PK_ID`=?",
					Statement.NO_GENERATED_KEYS);
			preparedStatement.setInt(1, pID);
			preparedStatement.executeUpdate();

		} catch (SQLException sqle) {
			log.log(Level.SEVERE, null, sqle);
		}
	}

	/**
	 * Set percentage of people who use given ITS. If percentage is set higher
	 * than 1.0 (100%) then {@code usersPercent} is set automatically to 1.0.
	 * Similarily if percentage is lover than 0.0 (0%) then {@code usersPercent}
	 * is set automatically to 0.0.
	 * 
	 * @param itsName
	 *            name of ITS.
	 * @param usersPercent
	 *            percent of people using ITS between 1.0 and 0.0.
	 */
	public static void setItsUsers(String itsName, double usersPercent) {
		int iID = getItsID(itsName);
		Connection con = null;
		Statement statement = null;

		if (usersPercent > 1)
			usersPercent = 1.0;
		else if (usersPercent < 0)
			usersPercent = 0.0;

		String query = "UPDATE TBL_ITS SET USERS=" + usersPercent
				+ " WHERE PK_ID=" + iID;

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			statement.executeUpdate(query);

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}
	}

	/**
	 * Set percent for given ITS and problem pair.
	 * 
	 * @param itsName
	 *            name of the ITS.
	 * @param problem
	 *            number of the problem.
	 * @param percent
	 *            by which given ITS solves a problem.
	 */
	public static void setPercentOfReduction(String itsName,
			String problemName, String subproblemName, double percent) {
		Connection con = null;
		Statement statement = null;
		int spID = getSubproblemID(problemName, subproblemName);
		int iID = getItsID(itsName);
		String query;
		if (itsHasDefinedReduction(itsName, problemName, subproblemName))
			query = "UPDATE TBL_ITS_SUBPROBLEM SET VALUE=" + percent
					+ " WHERE TBL_ITS_SUBPROBLEM.FK_ITS=" + iID
					+ " AND TBL_ITS_SUBPROBLEM.FK_SUBPROBLEM=" + spID;
		else
			query = "INSERT INTO `idss`.`TBL_ITS_SUBPROBLEM` (`VALUE`, `FK_ITS`, `FK_SUBPROBLEM`) VALUES ("
					+ percent + ", " + iID + ", " + spID + ")";

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			statement.executeUpdate(query);
			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}
	}

	/**
	 * Get available years for given problem name
	 * 
	 * @param problemName
	 *            name of problem
	 * @return array of strings with years in which problem has available data
	 */
	public static String[] getProblemYears(String problemName) {
		Connection con = null;
		Statement statement = null;
		String query = "SELECT * FROM(SELECT * FROM "
				+ "(SELECT * FROM idss.tbl_problem where name=\""
				+ problemName
				+ "\") "
				+ "t1 join idss.tbl_problem_subproblem on t1.PK_ID=idss.tbl_problem_subproblem.FK_PROBLEM)"
				+ " t2 join idss.tbl_subproblem_size on t2.FK_SUBPROBLEM=idss.tbl_subproblem_size.FK_SUBPROBLEM "
				+ "where t2.FK_SUBPROBLEM>5;";
		String[] names;
		ArrayList<String> problemYears = new ArrayList<String>();

		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);

			if (rs.next()) {
				do {
					problemYears.add(rs.getString("YEAR"));
				} while (rs.next());
			} else {
				problemYears.add("EMPTY");
			}

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		names = new String[problemYears.size()];
		return problemYears.toArray(names);
	}

	/**
	 * Gets value of problem for given problem in given year
	 * 
	 * @param problemName
	 *            string with problem name
	 * @param problemYear
	 *            string with problem year
	 * @return value of problem in searched year
	 */
	public static double getProblemValueforYear(String problemName,
			String problemYear) {

		if (problemYear.equals("EMPTY"))
			return 0.0;
		Connection con = null;
		Statement statement = null;
		String query = "SELECT VALUE FROM(SELECT * FROM "
				+ "(SELECT * FROM idss.tbl_problem where name=\""
				+ problemName
				+ "\") "
				+ "t1 join idss.tbl_problem_subproblem on t1.PK_ID=idss.tbl_problem_subproblem.FK_PROBLEM)"
				+ " t2 join idss.tbl_subproblem_size on t2.FK_SUBPROBLEM=idss.tbl_subproblem_size.FK_SUBPROBLEM "
				+ "where t2.FK_SUBPROBLEM>5 and tbl_subproblem_size.YEAR=\""
				+ problemYear + "\"";

		String value = null;
		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			rs.next();
			value = rs.getString("VALUE");
			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}

		return Double.parseDouble(value);
	}

	/**
	 * Sets problem value for given year
	 * 
	 * @param problemName
	 *            string representing problem name
	 * @param problemYear
	 *            string representing problem year
	 * @param newValue
	 *            new value of problem size to be updated in database
	 */
	public static void setProblemValueforYear(String problemName,
			String problemYear, double newValue) {
		Connection con = null;
		Statement statement = null;
		String query = "UPDATE tbl_subproblem_size SET VALUE=\"" + newValue
				+ "" + "\" WHERE YEAR=\"" + problemYear
				+ "\" and FK_SUBPROBLEM=(Select FK_SUBPROBLEM FROM "
				+ "(SELECT PK_ID FROM tbl_problem where NAME=\"" + problemName
				+ "\") t1 " + "join tbl_problem_subproblem "
				+ "on t1.PK_ID=tbl_problem_subproblem.FK_PROBLEM "
				+ "where tbl_problem_subproblem.FK_SUBPROBLEM>5)";
		try {
			con = getJDBCConnection();
			statement = con.createStatement();
			statement.executeUpdate(query);

			con.close();
		} catch (SQLException e) {
			log.log(Level.SEVERE, null, e);
		}
	}
}